import psycopg2
from lxml import etree


class Data(object):

    def __init__(self, name, desc):
        self.name = name
        self.desc = desc

    def toString(self):
        return "(" + self.name + "," + self.desc + ")"


def compose_data():
    data_list = []
    conn = psycopg2.connect(database="postgres", user="postgres",
                            password="123456", host="127.0.0.1", port="5432")
    cur = conn.cursor()
    with open(r'C:\Users\Administrator\Desktop\腾讯广告.html', 'r', encoding='utf-8') as f:
        tree = etree.HTML(f.read())
        contents = tree.xpath("//div[117]//table/tbody")
        for content in contents:
            trs = content.xpath("./tr")
            for tr in trs:
                td0 = tr.xpath(".//span/text()")
                # print(td0)
                # td1 = tr.xpath("./td[1]/text()")
                # print(td1)
                td1 = tr.xpath("./td[2]/text()")
                # print(td1)
                # data_list.append(Data(name=td0, desc=td1))
                save_for(cur=cur, _str1=td0[0], _str2=td1[0])
    # return data_list


def save_for(cur, _str1, _str2):
    _sql = "insert into tb1 values (1,'" + _str1 + "','" + _str2 + "')"
    print(_sql)
    cur.execute(_sql)


def save_data():
    conn = psycopg2.connect(database="postgres", user="postgres",
                            password="123456", host="127.0.0.1", port="5432")
    cursor = conn.cursor()
    cursor.execute("select * from area_dict")
    list = cursor.fetchall()
    for ls in list:
        print(ls)


def _select_(cur, sql):
    cur.execute(sql)
    return cur.fetchall()


def _insert_all_(_conn, _data):
    __list = _data["list"]
    _tb_name = _data["tbName"]
    _sql = "insert into " + _tb_name + "(name ,'desc') values "
    print(_sql)
    try:
        # batch = math.ceil(len(__list) / 10)
        cur = _conn.cursor()
        for _l in __list:
            _exe_sql = _sql + _l.toString()
            print(_exe_sql)
            cur.execute(_exe_sql)
            _conn.commit()
    except Exception as e:
        print(e)
        _conn.rollback()
    finally:
        _conn.close()
    print("execute end ....")


# cur.executemany(sql, _list)
# conn.commit()


# cur.execute(sql)


if __name__ == '__main__':
    # conn = psycopg2.connect(database="postgres", user="postgres",
    #                         password="123456", host="127.0.0.1", port="5432")
    compose_data()
    # _list = compose_data()

    # for _l in _list:
    #     print(_l.toString())
    #
    # data = {
    #     "list": _list,
    #     "tbName": "tb1"
    # }
    # _insert_all_(_conn=conn, _data=data)

# ## 连接到一个给定的数据库
# conn = psycopg2.connect(database="postgres", user="postgres",
#                         password="postgres", host="127.0.0.1", port="23333")
# ## 建立游标，用来执行数据库操作
# cursor = conn.cursor()
#
# ## 执行SQL命令
# cursor.execute("DROP TABLE test_conn")
# cursor.execute("CREATE TABLE test_conn(id int, name text)")
# cursor.execute("INSERT INTO test_conn values(1,'haha')")
#
# ## 提交SQL命令
# conn.commit()
#
#
# ## 关闭游标
# cursor.close()
#
# ## 关闭数据库连接
# conn.close()
